CREATE TABLE Dept(
departmentName VARCHAR2(15),
mgrId NUMBER(6), 
CONSTRAINT Dept_departName_pk PRIMARY KEY (departmentName));

CREATE TABLE Worker (
empId NUMBER(6), 
lastName VARCHAR2(20) NOT NULL,
firstName VARCHAR2(15) NOT NULL, 
departmentName VARCHAR2(15), 
birthDate DATE, 
hireDate DATE, 
salary NUMBER(8,2),
CONSTRAINT Worker_empid_pk PRIMARY KEY (empid),
CONSTRAINT Worker_departname_fk FOREIGN KEY (departmentName) REFERENCES Dept(departmentName));


CREATE TABLE Project(
projNo NUMBER(6), 
projName VARCHAR2(20), 
projMgrId NUMBER(6), 
budget NUMBER (8,2), 
startDate DATE, 
expectedDurationWeeks NUMBER(4),
CONSTRAINT Project_projNo_pk PRIMARY KEY (projNo),
CONSTRAINT Project_projMgrId_fk FOREIGN KEY(projMgrId) REFERENCES WORKER(empId));

CREATE TABLE Assign (
projNo NUMBER(6), 
empId NUMBER(6), 
hoursAssigned NUMBER(3), 
rating NUMBER(1),
CONSTRAINT Assign_projNo_empId_pk PRIMARY KEY (projNo, empId),
CONSTRAINT Assign_projNo_fk FOREIGN KEY (projNo) REFERENCES Project(projNo) ON DELETE CASCADE,
CONSTRAINT Assign_empId_fk FOREIGN KEY (empId) REFERENCES Worker(empId) ON DELETE CASCADE);


INSERT INTO Dept(departmentName) VALUES('Accounting');
INSERT INTO Dept(departmentName) VALUES('Research');


INSERT INTO Worker VALUES(101,'Smith','Tom', 'Accounting', '01-Feb-1960', '06-Jun-2009',50000);
INSERT INTO Worker VALUES(103,'Jones','Mary','Accounting', '15-Jun-1965','20-Sep-2010',48000);
INSERT INTO Worker VALUES(105,'Burns','Jane','Accounting', '21-Sep-1970','12-Jun-2008',39000);
INSERT INTO Worker VALUES(110,'Burns','Michael','Research', '05-Apr-1967','10-Sep-2009',70000);
INSERT INTO Worker VALUES(115,'Chin','Amanda','Research', '22-Sep-1965','19-Jun-2010',60000);

UPDATE Dept SET mgrId = 101 WHERE departmentName = 'Accounting';
UPDATE Dept SET mgrId = 110 WHERE departmentName = 'Research';	

INSERT INTO Project VALUES (1001, 'Jupiter', 101, 300000, '01-JUL-2009', 50);
INSERT INTO Project VALUES (1005, 'Saturn', 101, 400000, '01-JUL-2010', 35);
INSERT INTO Project VALUES (1019, 'Mercury', 110, 350000, '15-FEB-2010', 40);
INSERT INTO Project VALUES (1025, 'Neptune', 110, 600000, '01-FEB-2011', 45);
INSERT INTO Project VALUES (1030, 'Pluto', 110, 380000, '15-MAY-2011', 50);

INSERT INTO Assign(projNo,empId, hoursAssigned) VALUES(1001, 101, 30);
INSERT INTO Assign VALUES(1001, 103, 20,5);
INSERT INTO Assign(projNo,empId, hoursAssigned)  VALUES(1005, 103, 20);
INSERT INTO Assign(projNo,empId, hoursAssigned)  VALUES(1001, 105, 30);
INSERT INTO Assign VALUES(1001, 115, 20,4);
INSERT INTO Assign VALUES(1019, 110, 20,5);
INSERT INTO Assign VALUES(1019, 115, 10,4);
INSERT INTO Assign(projNo,empId, hoursAssigned)  VALUES(1025, 110, 10);
INSERT INTO Assign(projNo,empId, hoursAssigned)  VALUES(1030, 110, 10);
